# -*_ codeing=utf-8 -*-
# @Time: 2023/1/23 0:30
# @Author: foxhuty
# @File: to_mysql_with_small_data.py
# @Software: PyCharm
# @Based on python 3.10
import pandas as pd
from sqlalchemy import create_engine


def read_into_mysql(file_path, tb_name, db_name='db_ec'):
    """
    适合读入小型数据到Mysql.
    :param file_path: 文件名
    :param tb_name: 数据库表名
    :param db_name: 数据库名
    """
    engine = create_engine(f"mysql+mysqlconnector://huty_ec:foxmmer123@162.14.114.15:3306/{db_name}")
    conn = engine.connect()
    df = pd.read_excel(file_path,sheet_name='理科')
    # df = df[['word', 'phonetic', 'definition', 'translation']]
    print(df.shape)
    df.index.name = 'id'
    df.to_sql(name=tb_name, con=conn, if_exists='replace')
    conn.close()
    print('successfully saved to mysql')


if __name__ == '__main__':
    file = r'D:\年级管理数据\高2021级\高二上\高二上半期考试成绩分析/高2021级半期考试成绩数据.xlsx'
    read_into_mysql(file, 'tb_高2021级半期考试成绩数据_science')
